lyfz_repos/铂金版源码/DBFile/SQL/2015-08-13[收集]/2015-08-30.sql


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookDeputyStatisticNotFilmSelection')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookDeputyStatisticNotFilmSelection
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookDeputyStatisticNotFilmSelection]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookDeputyStatisticNotFilmSelection]
AS
SELECT  
tb_ErpOrder.ID, 
dbo.tb_ErpOrder.Ord_DividedShop, Ord_Type,
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 婚期, 
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.fn_ChineseToSpell((select stuff((select '',''+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,''''))) as 接单人拼音,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
CASE Ord_Type WHEN ''1'' THEN (SELECT TOP 1 Ordpg_Sights FROM tb_ErpOrdersPhotography WHERE   Ordpg_ViceNumber = Ordv_ViceNumber) ELSE '''' END AS 拍摄名称, 
(case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''2'') > 0 then ''拍摄中'' 
else case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''1'') > 0 then ''OK'' else ''未拍'' end end) AS 拍照状态,
dbo.fn_CheckOrderEarlyRepairStatus(dbo.tb_ErpOrderDigital.Ordv_EarlyRepairStatus) AS 初修状态, 
dbo.fn_CheckOrderFilmSelectionStatus(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionStatus) AS 选片状态, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_ReservationFilmSelectionName) AS 选片师, 
dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_ReservationFilmSelectionTime) AS 选片时间, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionDispatcher) AS 录入, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionDispatchTime)) AS 录入时间,
(SELECT   COUNT(Ordv_ViceNumber) AS Expr1
FROM      dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1
WHERE   (Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'')
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookDeputyStatisticNotLookDesign')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookDeputyStatisticNotLookDesign
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookDeputyStatisticNotLookDesign]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookDeputyStatisticNotLookDesign]
AS
SELECT   tb_ErpOrder.ID,
dbo.tb_ErpOrder.Ord_DividedShop, 
Ord_Type,
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 婚期, 
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.fn_ChineseToSpell((select stuff((select '',''+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,''''))) as 接单人拼音,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
CASE Ord_Type WHEN ''1'' THEN (SELECT TOP 1 Ordpg_Sights FROM tb_ErpOrdersPhotography WHERE Ordpg_ViceNumber = Ordv_ViceNumber) ELSE '''' END AS 拍摄名称, 
dbo.fn_CheckOrderDesignerStatus(dbo.tb_ErpOrderDigital.Ordv_DesignerStatus) AS 设计状态, 
dbo.fn_CheckOrderLookDesignStatus(dbo.tb_ErpOrderDigital.Ordv_LookDesignStatus) AS 看设计状态, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_ReservationLookDesignName) AS 看设计师, 
dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_ReservationLookDesignTime) AS 看设计时间, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_LookDesignDispatcher) AS 录入者, 
dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_LookDesignDispatchTime) AS 录入时间,
(SELECT COUNT(Ordv_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1 WHERE (Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2''))
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookDeputyStatisticNotPhotographed')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookDeputyStatisticNotPhotographed
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookDeputyStatisticNotPhotographed]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookDeputyStatisticNotPhotographed]
AS
SELECT   tb_ErpOrder.ID,
dbo.tb_ErpOrder.Ord_DividedShop,  Ord_Type,
dbo.tb_ErpOrdersPhotography.ID AS PID,
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 婚期, 
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.fn_ChineseToSpell((select stuff((select '',''+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,''''))) as 接单人拼音,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrder.Ord_CreateDateTime)) AS 订单日期, 
dbo.tb_ErpOrdersPhotography.Ordpg_Sights AS 拍摄名称, 
dbo.fn_CheckPhotographyStatus(dbo.tb_ErpOrdersPhotography.Ordpg_PhotographyStatus) AS 拍摄状态, 
dbo.fn_CheckDateTime(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationPhotographyTime) AS 摄影时间, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationPhotographyName) AS 摄影师, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationPhotographyAssistant) AS 摄影助理, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationMakeupArtist) AS 化妆师, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationMakeupAssistant) AS 化妆助理, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationBootDivision) AS 引导师, 
dbo.tb_ErpOrdersPhotography.Ordpg_Remark AS 备注,
(SELECT COUNT(tb_ErpOrdersPhotography_1.Ordpg_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrdersPhotography AS tb_ErpOrdersPhotography_1 LEFT OUTER JOIN dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1 ON 
tb_ErpOrdersPhotography_1.Ordpg_ViceNumber = tb_ErpOrderDigital_1.Ordv_ViceNumber WHERE (tb_ErpOrderDigital_1.Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number LEFT OUTER JOIN
dbo.tb_ErpOrdersPhotography ON 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber = dbo.tb_ErpOrdersPhotography.Ordpg_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2''))
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookDeputyStatisticNotPickup')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookDeputyStatisticNotPickup
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookDeputyStatisticNotPickup]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookDeputyStatisticNotPickup]
AS
SELECT   
tb_ErpOrder.ID,
dbo.tb_ErpOrder.Ord_DividedShop, Ord_Type,
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 婚期, 
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.fn_ChineseToSpell((select stuff((select '',''+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,''''))) as 接单人拼音,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
CASE WHEN Ord_Type = ''1'' THEN (SELECT TOP 1 Ordpg_Sights FROM tb_ErpOrdersPhotography WHERE Ordpg_ViceNumber = Ordv_ViceNumber) ELSE '''' END AS 拍摄名称,
tb_ErpOrderProductList.ID AS PPID,
OPlist_ProdName AS 商品名称,
dbo.fn_CheckProductTakePiecesStatus(OPlist_PickupStatus) AS 取件状态, 
dbo.fn_CheckProductCompletedStatus(OPlist_CompletedStatus) AS 完成状态, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(OPlist_ReservationTakeTime)) AS 取件日期,
OPlist_ReservationTakeName AS 取件人, 
dbo.fn_CheckIsExpedited(OPlist_IsExpedited) AS 加急,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(OPlist_ReservationTakeTime)) AS 加急日期
,Case when (select Count(ID) from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber)>0 then  (select Count(ID) from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber) else (SELECT COUNT(Ordv_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrderDigital WHERE Ordv_Number = dbo.tb_ErpOrder.Ord_Number) end   AS Ord_ViceOrderCount
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
Left Join tb_ErpOrderProductList ON OPlist_ViceNumber = Ordv_ViceNumber 
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2''))
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookDeputyStatisticNotSelectDress')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookDeputyStatisticNotSelectDress
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookDeputyStatisticNotSelectDress]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookDeputyStatisticNotSelectDress]
AS
SELECT   
tb_ErpOrder.ID,
dbo.tb_ErpOrder.Ord_DividedShop, Ord_Type,
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber,
Ordv_DigitalNumber, 
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型,
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 婚期, 
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.fn_ChineseToSpell((select stuff((select '',''+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,''''))) as 接单人拼音,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
CASE Ord_Type WHEN ''1'' THEN (SELECT TOP 1 Ordpg_Sights FROM tb_ErpOrdersPhotography WHERE Ordpg_ViceNumber = Ordv_ViceNumber) ELSE '''' END AS 拍摄名称, 
dbo.fn_CheckOrderClothingStatus(dbo.tb_ErpOrderDigital.Ordv_ClothingStatus) AS 选礼服状态, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_ReservationClothingName) AS 选礼服师, 
dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_ReservationClothingTime) AS 选礼服时间, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_ClothingDispatcher) AS 安排人, 
dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_ClothingDispatchTime) AS 安排时间,
(SELECT COUNT(Ordv_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1 WHERE (Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM dbo.tb_ErpOrder LEFT OUTER JOIN dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2''))
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookDeputyStatisticNotWedding')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookDeputyStatisticNotWedding
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookDeputyStatisticNotWedding]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookDeputyStatisticNotWedding]
AS
SELECT   tb_ErpOrder.ID,
dbo.tb_ErpOrder.Ord_DividedShop, 
dbo.tb_ErpWeddingService.ID AS WID, 
Ord_Type,
dbo.tb_ErpOrder.Ord_Number, 
0 AS Ordv_DigitalNumber,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 婚期, 
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.fn_ChineseToSpell((select stuff((select '',''+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,''''))) as 接单人拼音,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
dbo.tb_ErpWeddingService.Ws_ProdName AS 婚庆内容, 
dbo.fn_CheckWeddingServiceStatus(dbo.tb_ErpWeddingService.Ws_Status) AS 服务状态, 
dbo.fn_CheckDateTime(dbo.tb_ErpWeddingService.Ws_WeddingDate) AS 婚庆日期, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpWeddingService.Ws_Serviceman1) AS 服务人员1, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpWeddingService.Ws_Serviceman2) AS 服务人员2, 
dbo.tb_ErpWeddingService.Ws_Remark AS 服务备注,
(SELECT COUNT(Ws_Number) AS Expr1 FROM dbo.tb_ErpWeddingService AS tb_ErpWeddingService_1 WHERE (Ws_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpWeddingService ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpWeddingService.Ws_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'')
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookMainFilmSelection')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookMainFilmSelection
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookMainFilmSelection]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookMainFilmSelection]
AS
SELECT  
tb_ErpOrder.ID,
dbo.tb_ErpOrder.Ord_DividedShop, 
Ord_Type, 
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 婚期, 
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
(case when Ord_Type = ''1'' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else '''' end) as 拍摄名称,
(case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''2'') > 0 then ''拍摄中'' 
else case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''1'') > 0 then ''OK'' else ''未拍'' end end) AS 拍照状态,
dbo.fn_CheckOrderEarlyRepairStatus(dbo.tb_ErpOrderDigital.Ordv_EarlyRepairStatus) AS 初修状态, 
dbo.fn_CheckOrderFilmSelectionStatus(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionStatus) AS 选片状态, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_ReservationFilmSelectionName) AS 选片师, 
dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_ReservationFilmSelectionTime) AS 选片时间, 
dbo.tb_ErpOrderDigital.Ordv_ReservationFilmSelectionTime AS 选片时间查询, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionDispatcher) AS 安排人, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionDispatchTime)) AS 安排时间,
(SELECT COUNT(Ordv_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1 WHERE (Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2''))
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookMainLookDesign')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookMainLookDesign
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookMainLookDesign]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookMainLookDesign]
AS
SELECT   
tb_ErpOrder.ID,
dbo.tb_ErpOrder.Ord_DividedShop, 
Ord_Type,
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
CASE Ord_SinceOrderNumber WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 婚期, 
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
(case when Ord_Type = ''1'' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else '''' end) as 拍摄名称,
dbo.fn_CheckOrderLookDesignStatus(dbo.tb_ErpOrderDigital.Ordv_LookDesignStatus) AS 看设计状态, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_ReservationLookDesignName) AS 看设计师, 
dbo.fn_CheckDateTime(Ordv_ReservationLookDesignTime) AS 看设计时间, 
Ordv_ReservationLookDesignTime AS 看设计时间查询, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_LookDesignDispatcher) AS 安排人, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_LookDesignDispatchTime)) AS 安排时间,
(SELECT COUNT(Ordv_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1 WHERE (Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM dbo.tb_ErpOrder LEFT OUTER JOIN dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2''))
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookMainPhotographed')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookMainPhotographed
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookMainPhotographed]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookMainPhotographed]
AS
SELECT  tb_ErpOrder.ID, 
dbo.tb_ErpOrder.Ord_DividedShop, 
Ord_Type,
dbo.tb_ErpOrdersPhotography.ID AS VID, 
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 婚期,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
(Ord_SeriesPrice - (Case when (select sum(Pay_AmountOf) from dbo.tb_ErpPayment where Pay_OrdNumber =Ord_Number and Pay_Category in (''全款'',''后期收款'',''预约收款'',''预约补款'')) is null then 0 else (select sum(Pay_AmountOf) from dbo.tb_ErpPayment where Pay_OrdNumber =Ord_Number and Pay_Category in (''全款'',''后期收款'',''预约收款'',''预约补款'')) end) + (Select sum(Plu_Amount) as Plu_Amount from tb_ErpPlusPickItems where Plu_OrdNumber = Ord_Number)) AS 欠款,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
dbo.tb_ErpOrdersPhotography.Ordpg_Sights AS 拍摄名称, 
dbo.fn_CheckPhotographyStatus(Ordpg_PhotographyStatus) AS 拍摄状态,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordpg_ReservationPhotographyTime)) AS 摄影时间, 
Ordpg_ReservationPhotographyTime AS 摄影时间查询, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationPhotographyName) AS 摄影师, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationPhotographyAssistant) AS 摄影助理, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationMakeupArtist) AS 化妆师, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationMakeupAssistant) AS 化妆助理, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationBootDivision) AS 引导师, 
dbo.tb_ErpOrdersPhotography.Ordpg_Remark AS 录入备注, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_Dispatcher) AS 安排人, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrdersPhotography.Ordpg_DispatchTime)) AS 安排时间,
(SELECT COUNT(tb_ErpOrdersPhotography_1.Ordpg_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrdersPhotography AS tb_ErpOrdersPhotography_1 LEFT OUTER JOIN
dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1 ON tb_ErpOrdersPhotography_1.Ordpg_ViceNumber = tb_ErpOrderDigital_1.Ordv_ViceNumber WHERE (tb_ErpOrderDigital_1.Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number LEFT OUTER JOIN
dbo.tb_ErpOrdersPhotography ON 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber = dbo.tb_ErpOrdersPhotography.Ordpg_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2''))
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookMainPickup')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookMainPickup
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookMainPickup]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookMainPickup]
AS
SELECT   
dbo.tb_ErpOrder.ID,
dbo.tb_ErpOrder.Ord_DividedShop,Ord_Type, 
tb_ErpOrderProductList.ID AS PID,
dbo.tb_ErpOrder.Ord_Number, dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 婚期, 
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
CASE WHEN Ord_Type = ''1'' THEN (SELECT TOP 1 Ordpg_Sights FROM tb_ErpOrdersPhotography WHERE Ordpg_ViceNumber = Ordv_ViceNumber) ELSE '''' END AS 拍摄名称, 
dbo.tb_ErpOrderProductList.OPlist_ProdName AS 商品名称, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(OPlist_ReservationTakeTime)) AS 取件时间, 
OPlist_ReservationTakeTime AS 取件时间查询, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderProductList.OPlist_PickupName) AS 取件人, 
dbo.fn_CheckTakePiecesStatus(dbo.tb_ErpOrderProductList.OPlist_PickupStatus) AS 取件状态, 
dbo.fn_CheckProductCompletedStatus(dbo.tb_ErpOrderProductList.OPlist_CompletedStatus) AS 完成状态, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderProductList.OPlist_ArrangeNmae) AS 安排人, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderProductList.OPlist_ArrangeTime)) AS 安排时间
,Case when (select Count(ID) from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber)>0 then  (select Count(ID) from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber) else (SELECT COUNT(Ordv_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrderDigital WHERE Ordv_Number = dbo.tb_ErpOrder.Ord_Number) end   AS Ord_ViceOrderCount
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number LEFT OUTER JOIN
dbo.tb_ErpOrderProductList ON 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber = dbo.tb_ErpOrderProductList.OPlist_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrderProductList.OPlist_Type = ''2'') AND 
(dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2''))
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookMainSelectDress')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookMainSelectDress
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookMainSelectDress]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookMainSelectDress]
AS
SELECT   
tb_ErpOrder.ID,
dbo.tb_ErpOrder.Ord_DividedShop, 
Ord_Type, 
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber,
Ordv_DigitalNumber, 
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 婚期, 
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
(case when Ord_Type = ''1'' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else '''' end) as 拍摄名称,
dbo.fn_CheckOrderClothingStatus(dbo.tb_ErpOrderDigital.Ordv_ClothingStatus) AS 选礼服状态, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_ReservationClothingName) AS 选礼服师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_ReservationClothingTime)) AS 选礼服时间,  
dbo.tb_ErpOrderDigital.Ordv_ReservationClothingTime AS 选礼服时间查询, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_ClothingDispatcher) AS 安排人, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_ClothingDispatchTime)) AS 安排时间,
(SELECT COUNT(Ordv_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1 WHERE (Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2''))
' 
GO



IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookMainWedding')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookMainWedding
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookMainWedding]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookMainWedding]
AS
SELECT   
tb_ErpOrder.ID,
dbo.tb_ErpOrder.Ord_DividedShop, Ord_Type,
dbo.tb_ErpWeddingService.Ws_Type, 
dbo.tb_ErpWeddingService.ID AS VID, 
dbo.tb_ErpOrder.Ord_Number, 
0 AS Ordv_DigitalNumber,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号,  
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 婚期, 
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
dbo.tb_ErpWeddingService.Ws_ProdName AS 服务内容, 
dbo.fn_CheckWeddingServiceStatus(dbo.tb_ErpWeddingService.Ws_Status) AS 服务状态, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ws_WeddingDate)) AS 服务日期, 
Ws_WeddingDate AS 服务日期查询, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpWeddingService.Ws_Serviceman1) AS 服务人员1, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpWeddingService.Ws_Serviceman2) AS 服务人员2,
dbo.tb_ErpWeddingService.Ws_Remark AS 服务备注,
(SELECT COUNT(Ws_Number) AS Expr1 FROM dbo.tb_ErpWeddingService AS tb_ErpWeddingService_1 WHERE (Ws_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM    dbo.tb_ErpOrder LEFT OUTER JOIN dbo.tb_ErpWeddingService ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpWeddingService.Ws_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') 
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityDesign')
BEGIN
	DROP VIEW [dbo].BView_DoorCityDesign
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityDesign]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_DoorCityDesign]
AS
SELECT   
tb_ErpOrder.ID, 
dbo.tb_ErpOrder.Ord_DividedShop, 
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
Ord_Type,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.fn_ChineseToSpell((select stuff((select '',''+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,''''))) as 接单人拼音,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
dbo.fn_GetProductExpedited(dbo.tb_ErpOrderDigital.Ordv_ViceNumber) AS 加急, 
(case when (select count(OPlist_IsExpedited) from dbo.tb_ErpOrderProductList where OPlist_ViceNumber =Ordv_ViceNumber and OPlist_Type=''2'' and OPlist_IsExpedited=''1'') = 0 then '''' else
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime((select top 1 OPlist_ExpeditedTime from dbo.tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type=''2'' and OPlist_ExpeditedTime != '''' order by OPlist_ExpeditedTime ASC))) end ) AS 加急日期, 
(case when Ord_Type = ''1'' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else '''' end) as 拍摄名称,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName(Ordpg_Photographer) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_Photographer!= '''' group by Ordpg_Photographer for xml path('''')),1,1,'''')) as 摄影师,
(case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''2'') > 0 then ''拍摄中'' 
else case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''1'') > 0 then ''OK'' else ''未拍'' end end) AS 拍照状态,
dbo.fn_CheckOrderEarlyRepairStatus(dbo.tb_ErpOrderDigital.Ordv_EarlyRepairStatus) AS 初修状态, 
dbo.fn_CheckOrderFilmSelectionStatus(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionStatus) AS 选片状态, 
dbo.fn_CheckOrderRefinementStatus(dbo.tb_ErpOrderDigital.Ordv_RefinementStatus) AS 精修状态, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_RefinementTime)) AS 精修日期, 
dbo.fn_CheckOrderDesignerStatus(dbo.tb_ErpOrderDigital.Ordv_DesignerStatus) AS 设计状态, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_DesignerName) AS 设计师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_DesignerTime)) AS 设计日期, 
dbo.tb_ErpOrderDigital.Ordv_DesignerTime AS 设计查询日期, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_LookDesignName) AS 看设计师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_LookDesignTime)) AS 看设计日期, 
dbo.fn_CheckOrderLookDesignStatus(dbo.tb_ErpOrderDigital.Ordv_LookDesignStatus) AS 看设计状态, 
dbo.tb_ErpOrderDigital.Ordv_LookDesignClaim AS 看设计要求, 
(SELECT   COUNT(Ordv_ViceNumber) AS Expr1
FROM      dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1
WHERE   (Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'')
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityDigitalArrange')
BEGIN
	DROP VIEW [dbo].BView_DoorCityDigitalArrange
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityDigitalArrange]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_DoorCityDigitalArrange]
AS
SELECT   
tb_ErpOrder.ID, 
dbo.tb_ErpOrder.Ord_DividedShop, 
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
Ord_Type,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.fn_ChineseToSpell((select stuff((select '',''+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,''''))) as 接单人拼音,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
(case when Ord_Type = ''1'' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else '''' end) as 拍摄名称,
--dbo.fn_GetPhotographerStatus(dbo.tb_ErpOrderDigital.Ordv_ViceNumber) AS 拍摄状态, 
(case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''2'') > 0 then ''拍摄中'' 
else case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''1'') > 0 then ''OK'' else ''未拍'' end end) AS 拍照状态,
dbo.fn_CheckOrderEarlyRepairStatus(dbo.tb_ErpOrderDigital.Ordv_EarlyRepairStatus) + ''/'' + dbo.fn_CheckUserIDGetUserName((case when Ordv_EarlyRepairName <> '''' then Ordv_EarlyRepairName else Ordv_ReservationEarlyRepairName end)) AS 初修状态, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_ReservationEarlyRepairTime)) AS 初修日期, 
dbo.fn_CheckOrderFilmSelectionStatus(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionStatus) + ''/'' + dbo.fn_CheckUserIDGetUserName((case when Ordv_FilmSelectionName <> '''' then Ordv_FilmSelectionName else Ordv_ReservationFilmSelectionName end)) AS 选片状态, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionTime)) AS 选片日期, 
dbo.tb_ErpOrderDigital.Ordv_FilmSelectionTime AS 选片查询日期, 
dbo.fn_CheckOrderDesignerStatus(dbo.tb_ErpOrderDigital.Ordv_DesignerStatus) + ''/'' + dbo.fn_CheckUserIDGetUserName((case when Ordv_DesignerName <> '''' then Ordv_DesignerName else Ordv_ReservationDesignerName end)) AS 设计状态, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_ReservationDesignerTime)) AS 设计日期, 
dbo.fn_CheckOrderRefinementStatus(dbo.tb_ErpOrderDigital.Ordv_RefinementStatus) + ''/'' + dbo.fn_CheckUserIDGetUserName((case when Ordv_RefinementName <> '''' then Ordv_RefinementName else Ordv_ReservationRefinementName end)) AS 精修状态, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_ReservationRefinementTime)) AS 精修日期,  
dbo.fn_CheckOrderLookDesignStatus(dbo.tb_ErpOrderDigital.Ordv_LookDesignStatus) + ''/'' + dbo.fn_CheckUserIDGetUserName((case when Ordv_LookDesignName <> '''' then Ordv_LookDesignName else Ordv_ReservationLookDesignName end)) AS 看设计状态, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_LookDesignTime)) AS 看设计日期, 
dbo.tb_ErpOrderDigital.Ordv_LookDesignTime AS 看设计查询日期, 
dbo.fn_GetProductExpedited(dbo.tb_ErpOrderDigital.Ordv_ViceNumber) AS 加急, 
(case when (select count(OPlist_IsExpedited) from dbo.tb_ErpOrderProductList where OPlist_ViceNumber =Ordv_ViceNumber and OPlist_Type=''2'' and OPlist_IsExpedited=''1'') = 0 then '''' else
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime((select top 1 OPlist_ExpeditedTime from dbo.tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type=''2'' and OPlist_ExpeditedTime != '''' order by OPlist_ExpeditedTime ASC))) end ) AS 加急日期, 
(SELECT COUNT(Ordv_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1 WHERE (Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM dbo.tb_ErpOrder LEFT OUTER JOIN dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'')
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityFilmSelection')
BEGIN
	DROP VIEW [dbo].BView_DoorCityFilmSelection
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityFilmSelection]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_DoorCityFilmSelection]
AS
SELECT   
tb_ErpOrder.ID, 
dbo.tb_ErpOrder.Ord_DividedShop, 
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
Ord_Type,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.fn_ChineseToSpell((select stuff((select '',''+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,''''))) as 接单人拼音,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
dbo.tb_ErpOrder.Ord_PlusPickNumber AS 加挑张数, 
dbo.fn_GetPlusPickAmount(dbo.tb_ErpOrder.Ord_Number) AS 加挑金额, 
(case when Ord_Type = ''1'' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else '''' end) as 拍摄名称,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName(Ordpg_Photographer) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_Photographer!= '''' group by Ordpg_Photographer for xml path('''')),1,1,'''')) as 摄影师,
(case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''2'') > 0 then ''拍摄中'' 
else case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''1'') > 0 then ''OK'' else ''未拍'' end end) AS 拍照状态,
dbo.fn_CheckOrderEarlyRepairStatus(dbo.tb_ErpOrderDigital.Ordv_EarlyRepairStatus) AS 初修状态, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_EarlyRepairTime)) AS 初修日期, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionName) AS 选片师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionTime)) AS 选片日期, 
dbo.tb_ErpOrderDigital.Ordv_FilmSelectionTime AS 选片日期查询, 
dbo.fn_CheckOrderFilmSelectionStatus(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionStatus) AS 选片状态, 
dbo.fn_CheckOrderDesignerStatus(dbo.tb_ErpOrderDigital.Ordv_DesignerStatus) AS 设计状态, 
dbo.fn_CheckOrderRefinementStatus(dbo.tb_ErpOrderDigital.Ordv_RefinementStatus) AS 精修状态, 
dbo.fn_CheckOrderLookDesignStatus(dbo.tb_ErpOrderDigital.Ordv_LookDesignStatus) AS 看设计状态, 
(SELECT COUNT(Ordv_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1 WHERE (Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM dbo.tb_ErpOrder LEFT OUTER JOIN dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE (dbo.tb_ErpOrder.Ord_Class = ''1'') 
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityKePan')
BEGIN
	DROP VIEW [dbo].BView_DoorCityKePan
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityKePan]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_DoorCityKePan]
AS
SELECT   
tb_ErpOrder.ID, 
dbo.tb_ErpOrder.Ord_DividedShop, 
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
Ord_Type,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.fn_ChineseToSpell((select stuff((select '',''+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,''''))) as 接单人拼音,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
dbo.fn_GetProductExpedited(dbo.tb_ErpOrderDigital.Ordv_ViceNumber) AS 加急, 
(case when (select count(OPlist_IsExpedited) from dbo.tb_ErpOrderProductList where OPlist_ViceNumber =Ordv_ViceNumber and OPlist_Type=''2'' and OPlist_IsExpedited=''1'') = 0 then '''' else
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime((select top 1 OPlist_ExpeditedTime from dbo.tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type=''2'' and OPlist_ExpeditedTime != '''' order by OPlist_ExpeditedTime ASC))) end ) AS 加急日期, 
(case when Ord_Type = ''1'' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else '''' end) as 拍摄名称,
(case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''2'') > 0 then ''拍摄中'' 
else case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''1'') > 0 then ''OK'' else ''未拍'' end end) AS 拍照状态,
dbo.fn_GetKePan(dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 1) AS 刻盘次数, 
dbo.fn_GetKePan(dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 2) AS 刻盘状态, 
dbo.fn_CheckOrderEarlyRepairStatus(dbo.tb_ErpOrderDigital.Ordv_EarlyRepairStatus) AS 初修状态, 
dbo.fn_CheckOrderFilmSelectionStatus(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionStatus) AS 选片状态, 
dbo.fn_CheckOrderRefinementStatus(dbo.tb_ErpOrderDigital.Ordv_RefinementStatus) AS 精修状态, 
dbo.fn_CheckOrderDesignerStatus(dbo.tb_ErpOrderDigital.Ordv_DesignerStatus) AS 设计状态, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_DesignerTime)) AS 设计日期, 
dbo.fn_CheckOrderLookDesignStatus(dbo.tb_ErpOrderDigital.Ordv_LookDesignStatus) AS 看设计状态, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_LookDesignTime)) AS 看设计日期,
(SELECT COUNT(Ordv_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1 WHERE (Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE (dbo.tb_ErpOrder.Ord_Class = ''1'')
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityPhotographed')
BEGIN
	DROP VIEW [dbo].BView_DoorCityPhotographed
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityPhotographed]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_DoorCityPhotographed]
AS
SELECT   
tb_ErpOrder.ID, dbo.tb_ErpOrder.Ord_DividedShop, dbo.tb_ErpOrdersPhotography.ID AS JID, 
dbo.tb_ErpOrder.Ord_Number, dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
dbo.tb_ErpOrder.Ord_CustomerSource, 
dbo.tb_ErpOrder.Ord_PhotographyCategory, Ord_Type,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型,
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.fn_ChineseToSpell((select stuff((select '',''+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,''''))) as 接单人拼音,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
dbo.tb_ErpOrdersPhotography.Ordpg_Sights AS 拍摄名称, 
dbo.fn_CheckOrderPhotographyStatus(dbo.tb_ErpOrdersPhotography.Ordpg_PhotographyStatus) AS 拍摄状态, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_Photographer) AS 摄影师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrdersPhotography.Ordpg_PhotographyTime)) AS 拍摄日期, 
dbo.tb_ErpOrdersPhotography.Ordpg_PhotographyTime AS 拍摄日期查询, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_PhotographyAssistant) AS 拍摄助理, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_MakeupArtist) AS 化妆师, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_MakeupAssistant) AS 化妆助理, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_BootDivision) AS 引导师,
dbo.fn_CheckOrderClothingStatus(dbo.tb_ErpOrderDigital.Ordv_ClothingStatus) AS 选衣状态, 
dbo.fn_CheckOrderEarlyRepairStatus(dbo.tb_ErpOrderDigital.Ordv_EarlyRepairStatus) AS 初修状态, 
dbo.fn_CheckOrderFilmSelectionStatus(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionStatus) AS 选片状态, 
dbo.fn_CheckOrderDesignerStatus(dbo.tb_ErpOrderDigital.Ordv_DesignerStatus) AS 设计状态, 
dbo.fn_CheckOrderRefinementStatus(dbo.tb_ErpOrderDigital.Ordv_RefinementStatus) AS 精修状态, 
dbo.fn_CheckOrderLookDesignStatus(dbo.tb_ErpOrderDigital.Ordv_LookDesignStatus) AS 看设计状态, 
(SELECT   COUNT(tb_ErpOrdersPhotography_1.Ordpg_ViceNumber) AS Expr1
FROM      dbo.tb_ErpOrdersPhotography AS tb_ErpOrdersPhotography_1 LEFT OUTER JOIN
dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1 ON 
tb_ErpOrdersPhotography_1.Ordpg_ViceNumber = tb_ErpOrderDigital_1.Ordv_ViceNumber
WHERE   (tb_ErpOrderDigital_1.Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number LEFT OUTER JOIN
dbo.tb_ErpOrdersPhotography ON 
dbo.tb_ErpOrdersPhotography.Ordpg_ViceNumber = dbo.tb_ErpOrderDigital.Ordv_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'')
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityPhotosPrints')
BEGIN
	DROP VIEW [dbo].BView_DoorCityPhotosPrints
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityPhotosPrints]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_DoorCityPhotosPrints]
AS
SELECT   
tb_ErpOrder.ID, 
dbo.tb_ErpOrder.Ord_DividedShop, 
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
Ord_Type,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
(select Cus_NamePinyin from tb_ErpCustomer where Ord_MainContact=Cus_CustomerNumber) as 姓名拼音1,
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.fn_ChineseToSpell((select stuff((select '',''+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,''''))) as 接单人拼音,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
dbo.fn_GetProductExpedited(dbo.tb_ErpOrderDigital.Ordv_ViceNumber) AS 加急, 
(case when (select count(OPlist_IsExpedited) from dbo.tb_ErpOrderProductList where OPlist_ViceNumber =Ordv_ViceNumber and OPlist_Type=''2'' and OPlist_IsExpedited=''1'') = 0 then '''' else
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime((select top 1 OPlist_ExpeditedTime from dbo.tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type=''2'' and OPlist_ExpeditedTime != '''' order by OPlist_ExpeditedTime ASC))) end ) AS 加急日期, 
(case when Ord_Type = ''1'' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else '''' end) as 拍摄名称,
(case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''2'') > 0 then ''拍摄中'' 
else case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''1'') > 0 then ''OK'' else ''未拍'' end end) AS 拍照状态,
dbo.fn_CheckOrderDesignerStatus(dbo.tb_ErpOrderDigital.Ordv_DesignerStatus) AS 设计状态, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_DesignerName) AS 设计师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_DesignerTime)) AS 设计日期, 
dbo.fn_CheckOrderLookDesignStatus(dbo.tb_ErpOrderDigital.Ordv_LookDesignStatus) AS 看设计状态, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_LookDesignName) AS 看设计师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_LookDesignTime)) AS 看设计日期 
,tb_ErpOrderPhotoPrints.ID AS PPID
,Opps_PhotoName AS 相片名称, Opps_PhotoQuantity AS 数量, Opps_PhotoArea AS 面积,Opps_SendVendor AS 发出状态, dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Opps_SendTime)) AS 发出日期, 
dbo.fn_CheckPhotoPrintsReworkStatus(Opps_ReworkStatus) AS 返工状态, dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Opps_ReworkTime)) AS 返工日期, 
dbo.fn_CheckProductCompletedStatus(Opps_CompletedStatus) AS 完成状态, dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Opps_CompletedTime)) AS 完成日期 
,Case when (select Count(ID) from tb_ErpOrderPhotoPrints where Opps_OrderNumber = Ordv_ViceNumber)>0 then  (select Count(ID) from tb_ErpOrderPhotoPrints where Opps_OrderNumber = Ordv_ViceNumber) else (SELECT COUNT(Ordv_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrderDigital WHERE Ordv_Number = dbo.tb_ErpOrder.Ord_Number) end   AS Ord_ViceOrderCount
FROM dbo.tb_ErpOrder LEFT OUTER JOIN dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
Left Join tb_ErpOrderPhotoPrints on Opps_OrderNumber = Ordv_ViceNumber 
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'')
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityPhotosPrintsMonthPrint')
BEGIN
	DROP VIEW [dbo].BView_DoorCityPhotosPrintsMonthPrint
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityPhotosPrintsMonthPrint]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_DoorCityPhotosPrintsMonthPrint]
AS
SELECT   
tb_ErpOrder.ID,  dbo.tb_ErpOrder.Ord_DividedShop, 
dbo.tb_ErpOrderPhotoPrints.ID AS PPID,Ord_Number, Ordv_ViceNumber,
Ordv_DigitalNumber,
Ord_Type,
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号,
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
(case when Ord_Type = ''1'' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber ) else '''' end) AS 拍摄名称,
dbo.tb_ErpOrderPhotoPrints.Opps_PhotoName AS 相片名称, dbo.tb_ErpOrderPhotoPrints.Opps_PhotoQuantity AS 数量, 
dbo.tb_ErpOrderPhotoPrints.Opps_PhotoArea AS 面积m2, dbo.tb_ErpOrderPhotoPrints.Opps_SendVendor AS 是否发出, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderPhotoPrints.Opps_SendTime)) AS 发出日期, 
dbo.tb_ErpOrderPhotoPrints.Opps_SendTime AS 发出日期查询, 
dbo.fn_CheckProductCompletedStatus(dbo.tb_ErpOrderPhotoPrints.Opps_CompletedStatus) AS 是否完成, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderPhotoPrints.Opps_CompletedTime)) AS 完成日期, 
dbo.tb_ErpOrderPhotoPrints.Opps_CompletedTime AS 完成日期查询
,(select Count(ID) from tb_ErpOrderPhotoPrints where Opps_OrderNumber = Ordv_ViceNumber) AS Ord_ViceOrderCount
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number LEFT OUTER JOIN
dbo.tb_ErpOrderPhotoPrints ON 
dbo.tb_ErpOrderPhotoPrints.Opps_OrderNumber = dbo.tb_ErpOrderDigital.Ordv_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'')
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityRefinedRepair')
BEGIN
	DROP VIEW [dbo].BView_DoorCityRefinedRepair
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityRefinedRepair]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_DoorCityRefinedRepair]
AS
SELECT 
tb_ErpOrder.ID, 
dbo.tb_ErpOrder.Ord_DividedShop, 
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
Ord_Type,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.fn_ChineseToSpell((select stuff((select '',''+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,''''))) as 接单人拼音,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
dbo.fn_GetProductExpedited(dbo.tb_ErpOrderDigital.Ordv_ViceNumber) AS 加急, 
(case when (select count(OPlist_IsExpedited) from dbo.tb_ErpOrderProductList where OPlist_ViceNumber =Ordv_ViceNumber and OPlist_Type=''2'' and OPlist_IsExpedited=''1'') = 0 then '''' else
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime((select top 1 OPlist_ExpeditedTime from dbo.tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type=''2'' and OPlist_ExpeditedTime != '''' order by OPlist_ExpeditedTime ASC))) end ) AS 加急日期, 
(case when Ord_Type = ''1'' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else '''' end) as 拍摄名称,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName(Ordpg_Photographer) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_Photographer!= '''' group by Ordpg_Photographer for xml path('''')),1,1,'''')) as 摄影师,
(case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''2'') > 0 then ''拍摄中'' 
else case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''1'') > 0 then ''OK'' else ''未拍'' end end) AS 拍照状态,
dbo.fn_CheckOrderEarlyRepairStatus(dbo.tb_ErpOrderDigital.Ordv_EarlyRepairStatus) AS 初修状态, 
dbo.fn_CheckOrderFilmSelectionStatus(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionStatus) AS 选片状态, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionTime)) AS 选片日期, 
dbo.fn_CheckOrderDesignerStatus(dbo.tb_ErpOrderDigital.Ordv_DesignerStatus) AS 设计状态, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_RefinementName) AS 精修师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_RefinementTime)) AS 精修日期, 
dbo.tb_ErpOrderDigital.Ordv_RefinementTime AS 精修日期查询, 
dbo.fn_CheckOrderRefinementStatus(dbo.tb_ErpOrderDigital.Ordv_RefinementStatus) AS 精修状态, 
dbo.fn_CheckOrderLookDesignStatus(dbo.tb_ErpOrderDigital.Ordv_LookDesignStatus) AS 看设计状态, 
(SELECT COUNT(Ordv_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1 WHERE (Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount 
FROM dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') 
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityRepairPiece')
BEGIN
	DROP VIEW [dbo].BView_DoorCityRepairPiece
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityRepairPiece]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_DoorCityRepairPiece]
AS
SELECT   
tb_ErpOrder.ID, dbo.tb_ErpOrder.Ord_DividedShop, dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber,
Ordv_DigitalNumber, 
Ord_Type,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型,
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.fn_ChineseToSpell((select stuff((select '',''+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,''''))) as 接单人拼音,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
dbo.fn_GetProductExpedited(dbo.tb_ErpOrderDigital.Ordv_ViceNumber) AS 加急, 
(case when Ord_Type = ''1'' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else '''' end) as 拍摄名称,
(case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''2'') > 0 then ''拍摄中'' 
else case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''1'') > 0 then ''OK'' else ''未拍'' end end) AS 拍照状态,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName(Ordpg_Photographer) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_Photographer!= '''' group by Ordpg_Photographer for xml path('''')),1,1,'''')) as 摄影师,
(select top 1 dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordpg_PhotographyTime)) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyTime is not null order by Ordpg_PhotographyTime DESC) AS 拍摄日期, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_EarlyRepairName) AS 初修师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_EarlyRepairTime)) AS 初修日期, 
dbo.tb_ErpOrderDigital.Ordv_EarlyRepairTime AS 初修日期查询, 
dbo.fn_CheckOrderEarlyRepairStatus(dbo.tb_ErpOrderDigital.Ordv_EarlyRepairStatus) AS 初修状态, 
dbo.fn_CheckOrderFilmSelectionStatus(dbo.tb_ErpOrderDigital.Ordv_FilmSelectionStatus) AS 选片状态, 
dbo.fn_CheckOrderDesignerStatus(dbo.tb_ErpOrderDigital.Ordv_DesignerStatus) AS 设计状态, 
dbo.fn_CheckOrderRefinementStatus(dbo.tb_ErpOrderDigital.Ordv_RefinementStatus) AS 精修状态, 
dbo.fn_CheckOrderLookDesignStatus(dbo.tb_ErpOrderDigital.Ordv_LookDesignStatus) AS 看设计状态, 
(SELECT   COUNT(Ordv_ViceNumber) AS Expr1
FROM      dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1
WHERE   (Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') 
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityTotakePiece')
BEGIN
	DROP VIEW [dbo].BView_DoorCityTotakePiece
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityTotakePiece]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_DoorCityTotakePiece]
AS
SELECT   
tb_ErpOrder.ID, 
dbo.tb_ErpOrder.Ord_DividedShop, 
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
Ord_Type,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.fn_ChineseToSpell((select stuff((select '',''+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,''''))) as 接单人拼音, 
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
(Ord_SeriesPrice - (Case when (select sum(Pay_AmountOf) from dbo.tb_ErpPayment where Pay_OrdNumber =Ord_Number and Pay_Category in (''全款'',''后期收款'',''预约收款'',''预约补款'')) is null then 0 else (select sum(Pay_AmountOf) from dbo.tb_ErpPayment where Pay_OrdNumber =Ord_Number and Pay_Category in (''全款'',''后期收款'',''预约收款'',''预约补款'')) end) + (Select sum(Plu_Amount) as Plu_Amount from tb_ErpPlusPickItems where Plu_OrdNumber = Ord_Number)) AS 欠款,
(case when Ord_Type = ''1'' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else '''' end) as 拍摄名称,
(case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''2'') > 0 then ''拍摄中'' 
else case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''1'') > 0 then ''OK'' else ''未拍'' end end) AS 拍照状态,
dbo.fn_CheckOrderDesignerStatus(dbo.tb_ErpOrderDigital.Ordv_DesignerStatus) AS 设计状态, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_DesignerName) AS 设计师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_DesignerTime)) AS 设计日期, 
dbo.fn_CheckOrderLookDesignStatus(dbo.tb_ErpOrderDigital.Ordv_LookDesignStatus) AS 看设计状态, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_LookDesignName) AS 看设计师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_LookDesignTime)) AS 看设计日期, 
(case when (select count(OPlist_CompletedStatus) from dbo.tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = ''2'' and OPlist_CompletedStatus = ''0'' and OPlist_CompletedStatus = ''1'')>0 then ''未完'' else 
(case when (select count(OPlist_CompletedStatus) from dbo.tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = ''2'' and OPlist_CompletedStatus = ''0'') > 0 then ''未完'' else 
(case when (select count(OPlist_CompletedStatus) from dbo.tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = ''2'' and OPlist_CompletedStatus = ''1'') > 0 then ''OK'' else '''' end )end )end ) AS 是否完成,
(select top 1 dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(OPlist_PickupTime)) from dbo.tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type=''2'' and OPlist_PickupTime != '''' order by OPlist_PickupTime DESC) AS 取件日期,
(select top 1 OPlist_PickupTime from dbo.tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type=''2'' and OPlist_PickupTime != '''' order by OPlist_PickupTime DESC) AS 取件日期查询,
tb_ErpOrderProductList.ID AS OPID,
OPlist_ProdName AS 商品名称,
dbo.fn_CheckIsExpedited(OPlist_IsExpedited) AS 加急,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(OPlist_PickupTime)) AS 商品取件日期,
OPlist_SendVendor AS 是否发出,
dbo.fn_CheckProductReworkStatus(OPlist_ReworkStatus) AS 是否返工,
dbo.fn_CheckProductCompletedStatus(OPlist_CompletedStatus) AS 商品完成状态, 
dbo.fn_CheckProductTakePiecesStatus(OPlist_PickupStatus) AS 是否取走
,Case when (select Count(ID) from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber)>0 then  (select Count(ID) from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber) else (SELECT COUNT(Ordv_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrderDigital WHERE Ordv_Number = dbo.tb_ErpOrder.Ord_Number) end   AS Ord_ViceOrderCount
FROM dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
Left Join tb_ErpOrderProductList ON OPlist_ViceNumber = Ordv_ViceNumber 
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'')
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityTotakePieceMonthPieces')
BEGIN
	DROP VIEW [dbo].BView_DoorCityTotakePieceMonthPieces
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityTotakePieceMonthPieces]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_DoorCityTotakePieceMonthPieces]
AS
SELECT   
tb_ErpOrder.ID, dbo.tb_ErpOrder.Ord_DividedShop, dbo.tb_ErpOrder.Ord_Type, dbo.tb_ErpOrderProductList.ID AS TPID, 
dbo.tb_ErpOrder.Ord_Number, dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号,
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_DesignerName) AS 设计师, 
OPlist_ProdNumber,
OPlist_ProdName AS 产品名称, 
OPlist_Class, 
(select Sc_ClassName from tb_ErpSystemCategory where Sc_ClassCode = OPlist_Class) AS 商品类别, 
dbo.tb_ErpOrderProductList.OPlist_ProdQuantity AS 数量, dbo.tb_ErpOrderProductList.OPlist_CostPrice AS 制作费用, 
dbo.tb_ErpOrderProductList.OPlist_SendVendor AS 是否发出, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderProductList.OPlist_SendTime)) AS 发出日期, 
dbo.tb_ErpOrderProductList.OPlist_SendTime AS 发出日期查询, 
dbo.fn_CheckProductCompletedStatus(dbo.tb_ErpOrderProductList.OPlist_CompletedStatus) AS 是否完成, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderProductList.OPlist_CompletedTime)) AS 完成日期,
dbo.tb_ErpOrderProductList.OPlist_CompletedTime AS 完成日期查询,
(select Count(ID) from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber AND OPlist_Type = ''2'') AS Ord_ViceOrderCount
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number LEFT OUTER JOIN
dbo.tb_ErpOrderProductList ON 
dbo.tb_ErpOrderProductList.OPlist_ViceNumber = dbo.tb_ErpOrderDigital.Ordv_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrderProductList.OPlist_Type = ''2'')
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityTotakePieceMonthPiecesNotBack')
BEGIN
	DROP VIEW [dbo].BView_DoorCityTotakePieceMonthPiecesNotBack
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityTotakePieceMonthPiecesNotBack]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_DoorCityTotakePieceMonthPiecesNotBack]
AS
SELECT   
tb_ErpOrder.ID, dbo.tb_ErpOrder.Ord_DividedShop, dbo.tb_ErpOrder.Ord_Type,tb_ErpOrderProductList_1.ID AS PPID, dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号,
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
tb_ErpOrderProductList_1.OPlist_ProdNumber, tb_ErpOrderProductList_1.OPlist_ProdName AS 产品名称, 
tb_ErpOrderProductList_1.OPlist_Class,
(SELECT   Sc_ClassName
FROM      dbo.tb_ErpSystemCategory
WHERE   (Sc_ClassCode = tb_ErpOrderProductList_1.OPlist_Class)) AS 商品类别, 
tb_ErpOrderProductList_1.OPlist_ProdQuantity AS 数量, tb_ErpOrderProductList_1.OPlist_CostPrice AS 制作费用, 
tb_ErpOrderProductList_1.OPlist_SendVendor AS 是否发出, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(tb_ErpOrderProductList_1.OPlist_SendTime)) AS 发出日期, 
tb_ErpOrderProductList_1.OPlist_SendTime AS 发出日期查询, 
dbo.fn_CheckProductCompletedStatus(tb_ErpOrderProductList_1.OPlist_CompletedStatus) AS 是否完成, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(tb_ErpOrderProductList_1.OPlist_CompletedTime)) AS 完成日期,
(SELECT   COUNT(ID) AS Expr1 FROM      dbo.tb_ErpOrderProductList
WHERE   (OPlist_ViceNumber = dbo.tb_ErpOrderDigital.Ordv_ViceNumber) AND (OPlist_Type = ''2'')) AS Ord_ViceOrderCount
FROM  dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number LEFT OUTER JOIN
dbo.tb_ErpOrderProductList AS tb_ErpOrderProductList_1 ON 
tb_ErpOrderProductList_1.OPlist_ViceNumber = dbo.tb_ErpOrderDigital.Ordv_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE  (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (tb_ErpOrderProductList_1.OPlist_Type = ''2'')
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityTotakePieceMonthReworkStatistic')
BEGIN
	DROP VIEW [dbo].BView_DoorCityTotakePieceMonthReworkStatistic
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityTotakePieceMonthReworkStatistic]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_DoorCityTotakePieceMonthReworkStatistic]
AS
SELECT   
tb_ErpOrder.ID, dbo.tb_ErpOrder.Ord_DividedShop, dbo.tb_ErpOrder.Ord_Type, tb_ErpOrderProductList_1.ID AS PPID, 
dbo.tb_ErpOrder.Ord_Number, dbo.tb_ErpOrderDigital.Ordv_ViceNumber,
Ordv_DigitalNumber, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号,
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
tb_ErpOrderProductList_1.OPlist_ProdNumber, tb_ErpOrderProductList_1.OPlist_ProdName AS 产品名称, 
tb_ErpOrderProductList_1.OPlist_Class,
(SELECT Sc_ClassName FROM dbo.tb_ErpSystemCategory WHERE (Sc_ClassCode = tb_ErpOrderProductList_1.OPlist_Class)) AS 商品类别, 
tb_ErpOrderProductList_1.OPlist_ProdQuantity AS 数量, tb_ErpOrderProductList_1.OPlist_SendVendor AS 是否发出, 
dbo.fn_CheckProductReworkStatus(tb_ErpOrderProductList_1.OPlist_ReworkStatus) 
+ CASE OPlist_ReworkRemark WHEN '''' THEN '''' ELSE ''/['' + OPlist_ReworkRemark + '']'' END AS 是否返工, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(tb_ErpOrderProductList_1.OPlist_ReworkTime)) AS 返工日期, 
tb_ErpOrderProductList_1.OPlist_ReworkTime AS 返工日期查询, 
dbo.fn_CheckProductCompletedStatus(tb_ErpOrderProductList_1.OPlist_CompletedStatus) AS 是否完成, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(tb_ErpOrderProductList_1.OPlist_CompletedTime)) AS 完成日期,
(SELECT   COUNT(ID) AS Expr1 FROM      dbo.tb_ErpOrderProductList
WHERE   (OPlist_ViceNumber = dbo.tb_ErpOrderDigital.Ordv_ViceNumber) AND (OPlist_Type = ''2'')) AS Ord_ViceOrderCount
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number LEFT OUTER JOIN
dbo.tb_ErpOrderProductList AS tb_ErpOrderProductList_1 ON 
tb_ErpOrderProductList_1.OPlist_ViceNumber = dbo.tb_ErpOrderDigital.Ordv_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (tb_ErpOrderProductList_1.OPlist_Type = ''2'')
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_OrderReport')
BEGIN
	DROP VIEW [dbo].BView_OrderReport
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_OrderReport]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_OrderReport]
AS
SELECT tb_ErpOrder.ID,dbo.tb_ErpOrder.Ord_DividedShop, dbo.tb_ErpOrder.Ord_Number, tb_ErpOrderDigital_1.Ordv_ViceNumber, 
dbo.tb_ErpOrder.Ord_Class, Ord_Type, Ord_OrderClass,Ordv_DigitalNumber,
CASE Ord_SinceOrderNumber WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
[Age_String] AS 年龄,
[Age_Day]  AS 天,
[Age_Year] AS 年,
dbo.tb_ErpOrder.Ord_PhotographyCategory AS 套系类别, 
dbo.tb_ErpOrder.Ord_CustomerSource AS 客户来源, 
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber = Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.fn_ChineseToSpell((select stuff((select '',''+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber = Ord_Number for xml path('''')),1,1,''''))) as 接单人拼音,
(case when Ord_Type = ''1'' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else '''' end) as 拍摄名称,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName(Ordpg_Photographer) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_Photographer!= '''' group by Ordpg_Photographer for xml path('''')),1,1,'''')) as 摄影师,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupArtist) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_MakeupArtist!= '''' group by Ordpg_MakeupArtist for xml path('''')),1,1,'''')) as 化妆师,
(select top 1 dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordpg_PhotographyTime)) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyTime is not null order by Ordpg_PhotographyTime DESC) AS 拍摄时间, 
(case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''2'') > 0 then ''拍摄中'' 
else case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''1'') > 0 then ''OK'' else ''未拍'' end end) AS 拍照状态,
dbo.fn_CheckUserIDGetUserName(tb_ErpOrderDigital_1.Ordv_EarlyRepairName) AS 初修师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordv_EarlyRepairTime)) AS 初修时间, 
dbo.fn_CheckOrderEarlyRepairStatus(tb_ErpOrderDigital_1.Ordv_EarlyRepairStatus) AS 初修状态, 
dbo.fn_CheckUserIDGetUserName(tb_ErpOrderDigital_1.Ordv_FilmSelectionName) AS 选片师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordv_FilmSelectionTime)) AS 选片时间, 
dbo.fn_CheckOrderFilmSelectionStatus(tb_ErpOrderDigital_1.Ordv_FilmSelectionStatus) AS 选片状态, 
dbo.fn_CheckUserIDGetUserName(tb_ErpOrderDigital_1.Ordv_DesignerName) AS 设计师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(tb_ErpOrderDigital_1.Ordv_DesignerTime)) AS 设计时间, 
dbo.fn_CheckOrderDesignerStatus(tb_ErpOrderDigital_1.Ordv_DesignerStatus) AS 设计状态, 
dbo.fn_CheckUserIDGetUserName(tb_ErpOrderDigital_1.Ordv_RefinementName) AS 精修师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(tb_ErpOrderDigital_1.Ordv_RefinementTime)) AS 精修时间, 
dbo.fn_CheckOrderRefinementStatus(tb_ErpOrderDigital_1.Ordv_RefinementStatus) AS 精修状态, 
dbo.fn_CheckUserIDGetUserName(tb_ErpOrderDigital_1.Ordv_LookDesignName) AS 看设计师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(tb_ErpOrderDigital_1.Ordv_LookDesignTime)) AS 看设计时间, 
dbo.fn_CheckOrderLookDesignStatus(tb_ErpOrderDigital_1.Ordv_LookDesignStatus) AS 看设计状态, 
tb_ErpOrderDigital_1.Ordv_LookDesignClaim AS 看设计要求, 
(case when (select count(OPlist_PickupStatus) from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = ''2'' and OPlist_PickupStatus = ''0'') >0  then ''未取'' else ''OK'' end) AS 取件状态, 
(select top 1 dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(OPlist_PickupTime)) from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = ''2'' and OPlist_PickupTime != null order by OPlist_PickupTime) AS 取件日期, 
dbo.tb_ErpOrder.Ord_Remark AS 备注, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrder.Ord_CreateName) AS 录入员, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ord_CreateDateTime)) AS 下单日期,
Ord_CreateDateTime AS 下单日期查询,
(SELECT COUNT(Ordv_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrderDigital WHERE (Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM 
dbo.tb_ErpOrder LEFT OUTER JOIN dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1 ON dbo.tb_ErpOrder.Ord_Number = tb_ErpOrderDigital_1.Ordv_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
where Ord_Type <> ''3''
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_ProcessMonitorNotBack')
BEGIN
	DROP VIEW [dbo].BView_ProcessMonitorNotBack
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_ProcessMonitorNotBack]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_ProcessMonitorNotBack]
AS
SELECT   
tb_ErpOrderProductList.ID AS PID,
dbo.tb_ErpOrder.ID, 
Ord_DividedShop, 
Ordv_ViceNumber AS Ord_Number,
Ord_Number AS Ordv_ViceNumber, 
Ordv_DigitalNumber,
Ord_Type, 
CASE Ord_SinceOrderNumber WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名, 
Cus_Sex_cs AS 客户性别, 
Cus_Telephone AS 客户电话, 
Ord_SeriesName AS 套系名称, 
Ord_SeriesPrice AS 套系价格, 
dbo.fn_GetOrderArrears(Ord_Number) AS 欠款,
(select top 1 Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) as 拍摄名称,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 结婚日期,
dbo.fn_CheckPhotoPrintsSendStatus(OPlist_SendStatus) AS 发片状态, 
OPlist_ProdName AS 商品名称,
dbo.fn_CheckProductCompletedStatus(OPlist_CompletedStatus) AS 完成状态, 
dbo.fn_CheckDateTime(OPlist_ReservationTakeTime) AS 取件日期, 
dbo.fn_CheckUserIDGetUserName(TR_TracePersonID) AS 客服人员, 
dbo.fn_CheckDateTime(TR_CreateDateTime) AS 录入时间, 
TR_Communicationstatus AS 沟通状态, 
TR_Remark AS 沟通备注, DATEDIFF(day, 
Ordv_DesignerTime, GETDATE()) AS 几天未完,
(select Count(ID) as DeCount from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = ''2'' and OPlist_SendStatus = ''1'' and OPlist_CompletedStatus != ''1'') AS Ord_ViceOrderCount
FROM dbo.tb_ErpOrder 
LEFT JOIN dbo.tb_ErpOrderDigital ON Ord_Number = Ordv_Number 
Left JOIN dbo.tb_ErpOrderProductList ON OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = ''2'' and OPlist_SendStatus = ''1'' and OPlist_CompletedStatus != ''1''
LEFT JOIN dbo.tempTB_AggregationCustomer ON Ord_Number = GP_OrderNumber 
LEFT JOIN dbo.Vwp_GetLastClientTrackRecord ON TR_CustomerGroupID = Ordv_Number
WHERE Ord_Class = ''1'' and OPlist_SendStatus = ''1'' 
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_ProcessMonitorNotDesign')
BEGIN
	DROP VIEW [dbo].BView_ProcessMonitorNotDesign
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_ProcessMonitorNotDesign]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_ProcessMonitorNotDesign]
AS
SELECT   dbo.tb_ErpOrder.ID, 
Ord_DividedShop, 
Ord_Number, 
Ordv_ViceNumber,
Ordv_DigitalNumber,
Ord_Type, 
CASE Ord_SinceOrderNumber WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名, 
Cus_Sex_cs AS 客户性别, 
Cus_Telephone AS 客户电话, 
Ord_SeriesName AS 套系名称, 
Ord_SeriesPrice AS 套系价格, 
dbo.fn_GetOrderArrears(Ord_Number) AS 欠款, 
(select top 1 Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) as 拍摄名称,
dbo.fn_CheckOrderFilmSelectionStatus(Ordv_FilmSelectionStatus) AS 选片状态, 
dbo.fn_CheckOrderDesignerStatus(Ordv_DesignerStatus) AS 设计状态, 
dbo.fn_CheckUserIDGetUserName(Ordv_ReservationDesignerName) AS 设计师, 
dbo.fn_CheckDateTime(Ordv_ReservationDesignerTime) AS 预定时间, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 结婚日期, 
(SELECT TOP (1) dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(OPlist_PickupTime)) AS Expr1 FROM dbo.tb_ErpOrderProductList WHERE 
(OPlist_ViceNumber = dbo.tb_ErpOrderDigital.Ordv_ViceNumber) AND (OPlist_Type = ''2'') AND (OPlist_PickupTime <> NULL) ORDER BY OPlist_PickupTime) AS 取件日期, 
dbo.fn_CheckUserIDGetUserName(TR_TracePersonID) AS 客服人员, 
dbo.fn_CheckDateTime(TR_CreateDateTime) AS 录入时间, 
TR_Communicationstatus AS 沟通状态, 
TR_Remark AS 沟通备注, 
DATEDIFF(day, Ordv_FilmSelectionTime, GETDATE()) AS 几天未完
FROM dbo.tb_ErpOrder 
LEFT OUTER JOIN dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number 
LEFT OUTER JOIN dbo.tempTB_AggregationCustomer ON dbo.tb_ErpOrder.Ord_Number = dbo.tempTB_AggregationCustomer.GP_OrderNumber 
LEFT OUTER JOIN dbo.Vwp_GetLastClientTrackRecord ON dbo.Vwp_GetLastClientTrackRecord.TR_CustomerGroupID = dbo.tb_ErpOrderDigital.Ordv_Number
WHERE 
dbo.tb_ErpOrder.Ord_Class = ''1'' AND dbo.tb_ErpOrderDigital.Ordv_FilmSelectionStatus = ''1'' AND dbo.tb_ErpOrderDigital.Ordv_DesignerStatus <> ''2''
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_ProcessMonitorNotLook')
BEGIN
	DROP VIEW [dbo].BView_ProcessMonitorNotLook
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_ProcessMonitorNotLook]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_ProcessMonitorNotLook]
AS
SELECT   
dbo.tb_ErpOrder.ID, 
Ord_DividedShop, 
Ord_Number, 
Ordv_ViceNumber, 
Ordv_DigitalNumber,
Ord_Type, 
CASE Ord_SinceOrderNumber WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名, 
Cus_Sex_cs AS 客户性别, 
Cus_Telephone AS 客户电话, 
Ord_SeriesName AS 套系名称, 
Ord_SeriesPrice AS 套系价格, 
dbo.fn_GetOrderArrears(Ord_Number) AS 欠款, 
(select top 1 Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) as 拍摄名称,
dbo.fn_CheckOrderDesignerStatus(Ordv_DesignerStatus) AS 设计状态, 
dbo.fn_CheckOrderLookDesignStatus(Ordv_LookDesignStatus) AS 看样状态, 
dbo.fn_CheckUserIDGetUserName(Ordv_ReservationLookDesignName) AS 看样师, 
dbo.fn_CheckDateTime(Ordv_ReservationLookDesignTime) AS 预定时间, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 结婚日期,
(SELECT TOP (1) dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(OPlist_PickupTime)) AS Expr1 FROM dbo.tb_ErpOrderProductList WHERE 
(OPlist_ViceNumber = dbo.tb_ErpOrderDigital.Ordv_ViceNumber) AND (OPlist_Type = ''2'') AND (OPlist_PickupTime <> NULL) ORDER BY OPlist_PickupTime) AS 取件日期, 
dbo.fn_CheckUserIDGetUserName(TR_TracePersonID) AS 客服人员, 
dbo.fn_CheckDateTime(TR_CreateDateTime) AS 录入时间, 
TR_Communicationstatus AS 沟通状态, 
TR_Remark AS 沟通备注, 
DATEDIFF(day, Ordv_DesignerTime, GETDATE()) AS 几天未完
FROM dbo.tb_ErpOrder 
LEFT OUTER JOIN dbo.tb_ErpOrderDigital ON Ord_Number = Ordv_Number 
LEFT OUTER JOIN dbo.tempTB_AggregationCustomer ON Ord_Number = GP_OrderNumber 
LEFT OUTER JOIN dbo.Vwp_GetLastClientTrackRecord ON 
dbo.Vwp_GetLastClientTrackRecord.TR_CustomerGroupID = dbo.tb_ErpOrderDigital.Ordv_Number
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrderDigital.Ordv_DesignerStatus = ''2'') AND 
(dbo.tb_ErpOrderDigital.Ordv_LookDesignStatus <> ''1'')
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_ProcessMonitorNotPickup')
BEGIN
	DROP VIEW [dbo].BView_ProcessMonitorNotPickup
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_ProcessMonitorNotPickup]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_ProcessMonitorNotPickup]
AS
SELECT   
tb_ErpOrderProductList.ID AS PID,
dbo.tb_ErpOrder.ID, 
Ord_DividedShop, 
Ordv_ViceNumber AS Ord_Number,
Ord_Number AS Ordv_ViceNumber, 
Ordv_DigitalNumber,
Ord_Type, 
CASE Ord_SinceOrderNumber WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名, 
Cus_Sex_cs AS 客户性别, 
Cus_Telephone AS 客户电话, 
Ord_SeriesName AS 套系名称, 
Ord_SeriesPrice AS 套系价格, 
dbo.fn_GetOrderArrears(Ord_Number) AS 欠款,
(select top 1 Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) as 拍摄名称,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 结婚日期,
dbo.fn_CheckProductCompletedStatus(OPlist_CompletedStatus) AS 完成状态, 
OPlist_ProdName AS 商品名称,
dbo.fn_CheckProductTakePiecesStatus(OPlist_PickupStatus) AS 取件状态, 
dbo.fn_CheckDateTime(OPlist_ReservationTakeTime) AS 取件日期, 
dbo.fn_CheckUserIDGetUserName(TR_TracePersonID) AS 客服人员,
dbo.fn_CheckDateTime(TR_CreateDateTime) AS 录入时间, 
TR_Communicationstatus AS 沟通状态, 
TR_Remark AS 沟通备注, DATEDIFF(day, 
Ordv_DesignerTime, GETDATE()) AS 几天未完,
(select Count(ID) as DeCount from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = ''2'' and OPlist_CompletedStatus = ''1'' and OPlist_PickupStatus != ''1'') AS Ord_ViceOrderCount
FROM dbo.tb_ErpOrder 
LEFT JOIN dbo.tb_ErpOrderDigital ON Ord_Number = Ordv_Number 
Left JOIN dbo.tb_ErpOrderProductList ON OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = ''2'' and OPlist_CompletedStatus = ''1'' and OPlist_PickupStatus != ''1''
LEFT JOIN dbo.tempTB_AggregationCustomer ON Ord_Number = GP_OrderNumber 
LEFT JOIN dbo.Vwp_GetLastClientTrackRecord ON TR_CustomerGroupID = Ordv_Number
WHERE Ord_Class = ''1'' and OPlist_CompletedStatus = ''1'' 
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_ProcessMonitorNotRepair')
BEGIN
	DROP VIEW [dbo].BView_ProcessMonitorNotRepair
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_ProcessMonitorNotRepair]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_ProcessMonitorNotRepair]
AS
SELECT   
tb_ErpOrder.ID, 
Ord_DividedShop, 
Ord_Number, 
Ordv_ViceNumber,
Ordv_DigitalNumber,
Ord_Type,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
Ord_SeriesName AS 套系名称, 
Ord_SeriesPrice AS 套系价格, 
dbo.fn_GetOrderArrears(Ord_Number) AS 欠款,
(select top 1 Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) as 拍摄名称,
(case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''2'') > 0 then ''拍摄中'' 
else case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''1'') > 0 then ''OK'' else ''未拍'' end end) AS 拍照状态,
dbo.fn_CheckOrderEarlyRepairStatus(Ordv_EarlyRepairStatus) AS 修片状态, 
dbo.fn_CheckUserIDGetUserName(Ordv_ReservationEarlyRepairName) AS 修片师, 
dbo.fn_CheckDateTime(Ordv_ReservationEarlyRepairTime) AS 预定时间, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 结婚日期,
(select top 1 dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(OPlist_PickupTime)) from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = ''2'' and OPlist_PickupTime != null order by OPlist_PickupTime) AS 取件日期, 
dbo.fn_CheckUserIDGetUserName(TR_TracePersonID) AS 客服人员,
dbo.fn_CheckDateTime(TR_CreateDateTime) AS 录入时间,
TR_Communicationstatus AS 沟通状态,
TR_Remark AS 沟通备注,
(datediff(day, (select top 1 Ordpg_PhotographyTime from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyTime is not null order by Ordpg_PhotographyTime DESC), getdate())) AS 几天未完
FROM dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON Ord_Number = Ordv_Number
left join tempTB_AggregationCustomer on Ord_Number = GP_OrderNumber
Left join dbo.Vwp_GetLastClientTrackRecord on TR_CustomerGroupID = Ordv_Number
WHERE (Ord_Class = ''1'') and Ordv_EarlyRepairStatus != ''2''
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_ProcessMonitorNotSelected')
BEGIN
	DROP VIEW [dbo].BView_ProcessMonitorNotSelected
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_ProcessMonitorNotSelected]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_ProcessMonitorNotSelected]
AS
with tb AS
(
SELECT   dbo.tb_ErpOrder.ID, 
Ord_DividedShop, 
Ord_Number, 
Ordv_ViceNumber, 
Ordv_DigitalNumber,
Ord_Type, 
CASE Ord_SinceOrderNumber WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(Ord_Type) AS 订单类型, 
dbo.tempTB_AggregationCustomer.Cus_Name AS 客户姓名, 
dbo.tempTB_AggregationCustomer.Cus_Sex_cs AS 客户性别, 
dbo.tempTB_AggregationCustomer.Cus_Telephone AS 客户电话, 
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
dbo.fn_GetOrderArrears(dbo.tb_ErpOrder.Ord_Number) AS 欠款,
(select top 1 Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) as 拍摄名称,
(case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''2'') > 0 then ''拍摄中'' 
else case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''1'') > 0 then ''OK'' else ''未拍'' end end) AS 拍照状态,
dbo.fn_CheckOrderFilmSelectionStatus(Ordv_FilmSelectionStatus) AS 选片状态, 
dbo.fn_CheckUserIDGetUserName(Ordv_ReservationFilmSelectionName) AS 选片师, 
dbo.fn_CheckDateTime(Ordv_ReservationFilmSelectionTime) AS 预定时间, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 结婚日期,
(SELECT TOP (1) dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(OPlist_PickupTime)) AS Expr1 FROM dbo.tb_ErpOrderProductList WHERE (OPlist_ViceNumber = Ordv_ViceNumber) AND OPlist_Type = ''2'' AND OPlist_PickupTime <> NULL ORDER BY OPlist_PickupTime) AS 取件日期, 
dbo.fn_CheckUserIDGetUserName(TR_TracePersonID) AS 客服人员, 
dbo.fn_CheckDateTime(TR_CreateDateTime) AS 录入时间, 
TR_Communicationstatus AS 沟通状态, 
TR_Remark AS 沟通备注, DATEDIFF(day,
(SELECT TOP (1) Ordpg_PhotographyTime FROM dbo.tb_ErpOrdersPhotography WHERE Ordpg_ViceNumber = Ordv_ViceNumber AND Ordpg_PhotographyTime IS NOT NULL ORDER BY Ordpg_PhotographyTime DESC), GETDATE()) AS 几天未完
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON Ord_Number = Ordv_Number LEFT OUTER JOIN
dbo.tempTB_AggregationCustomer ON 
Ord_Number = GP_OrderNumber LEFT OUTER JOIN
Vwp_GetLastClientTrackRecord ON 
TR_CustomerGroupID = Ordv_Number
WHERE   Ord_Class = ''1'' and Ordv_FilmSelectionStatus != ''1''
)
select * from tb where 拍照状态 = ''OK''
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_ProcessMonitorNotSelected2')
BEGIN
	DROP VIEW [dbo].BView_ProcessMonitorNotSelected2
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_ProcessMonitorNotSelected2]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_ProcessMonitorNotSelected2]
AS
SELECT   tb_ErpOrder.ID, 
Ord_DividedShop, 
Ord_Number, 
Ordv_ViceNumber, 
Ordv_DigitalNumber,
Ord_Type, 
CASE Ord_SinceOrderNumber WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名, 
Cus_Sex_cs AS 客户性别, 
Cus_Telephone AS 客户电话, 
Ord_SeriesName AS 套系名称, 
Ord_SeriesPrice AS 套系价格, 
dbo.fn_GetOrderArrears(Ord_Number) AS 欠款, 
(select top 1 Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) as 拍摄名称,
--dbo.fn_CheckOrderEarlyRepairStatus(Ordv_EarlyRepairStatus) AS 修片状态, 
dbo.fn_CheckOrderFilmSelectionStatus(Ordv_FilmSelectionStatus) AS 选片状态, 
dbo.fn_CheckUserIDGetUserName(Ordv_ReservationFilmSelectionName) AS 选片师, 
dbo.fn_CheckDateTime(Ordv_ReservationFilmSelectionTime) AS 预定时间, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 结婚日期,
(SELECT TOP (1) dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(OPlist_PickupTime)) AS Expr1 FROM dbo.tb_ErpOrderProductList
WHERE (OPlist_ViceNumber = Ordv_ViceNumber) AND (OPlist_Type = ''2'') AND (OPlist_PickupTime <> NULL) ORDER BY OPlist_PickupTime) AS 取件日期, 
dbo.fn_CheckUserIDGetUserName(TR_TracePersonID) AS 客服人员, 
dbo.fn_CheckDateTime(TR_CreateDateTime) AS 录入时间, 
TR_Communicationstatus AS 沟通状态, 
TR_Remark AS 沟通备注, 
DATEDIFF(day, Ordv_EarlyRepairTime, GETDATE()) AS 几天未完
FROM dbo.tb_ErpOrder 
LEFT OUTER JOIN dbo.tb_ErpOrderDigital ON Ord_Number = Ordv_Number 
LEFT OUTER JOIN dbo.tempTB_AggregationCustomer ON  Ord_Number = GP_OrderNumber 
LEFT OUTER JOIN dbo.Vwp_GetLastClientTrackRecord ON TR_CustomerGroupID = Ordv_Number
WHERE   Ord_Class = ''1'' AND Ordv_EarlyRepairStatus = ''2'' AND Ordv_FilmSelectionStatus <> ''1''
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_ProcessMonitorNotSend')
BEGIN
	DROP VIEW [dbo].BView_ProcessMonitorNotSend
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_ProcessMonitorNotSend]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_ProcessMonitorNotSend]
AS
SELECT   
tb_ErpOrderProductList.ID AS PID,
dbo.tb_ErpOrder.ID, 
Ord_DividedShop, 
Ordv_ViceNumber AS Ord_Number,
Ord_Number AS Ordv_ViceNumber, 
Ordv_DigitalNumber,
Ord_Type, 
CASE Ord_SinceOrderNumber WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名, 
Cus_Sex_cs AS 客户性别, 
Cus_Telephone AS 客户电话, 
Ord_SeriesName AS 套系名称, 
Ord_SeriesPrice AS 套系价格, 
dbo.fn_GetOrderArrears(Ord_Number) AS 欠款,
(select top 1 Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) as 拍摄名称,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 结婚日期,
dbo.fn_CheckDateTime(Ordv_ReservationSendPhotoTime) AS 预定时间, 
dbo.fn_CheckPhotoPrintsSendStatus(OPlist_SendStatus) AS 发片状态, 
OPlist_ProdName AS 商品名称,
dbo.fn_CheckDateTime(OPlist_ReservationTakeTime) AS 取件日期, 
TR_TracePersonID AS 客服人员, 
dbo.fn_CheckDateTime(TR_CreateDateTime) AS 录入时间, 
TR_Communicationstatus AS 沟通状态, 
TR_Remark AS 沟通备注, DATEDIFF(day, 
Ordv_DesignerTime, GETDATE()) AS 几天未完,
(select Count(ID) as DeCount from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = ''2'' and OPlist_SendStatus = ''0'') AS Ord_ViceOrderCount
FROM dbo.tb_ErpOrder 
LEFT JOIN dbo.tb_ErpOrderDigital ON Ord_Number = Ordv_Number 
Left JOIN dbo.tb_ErpOrderProductList ON OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = ''2'' and OPlist_SendStatus = ''0''
LEFT JOIN dbo.tempTB_AggregationCustomer ON Ord_Number = GP_OrderNumber 
LEFT JOIN dbo.Vwp_GetLastClientTrackRecord ON TR_CustomerGroupID = Ordv_Number
WHERE Ord_Class = ''1'' and Ordv_LookDesignStatus = ''1'' 
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_ProcessMonitorNotShot')
BEGIN
	DROP VIEW [dbo].BView_ProcessMonitorNotShot
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_ProcessMonitorNotShot]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_ProcessMonitorNotShot]
AS
SELECT   
tb_ErpOrdersPhotography.ID AS PID,
tb_ErpOrder.ID,
Ord_DividedShop, 
Ord_Number, 
Ordv_ViceNumber, 
Ordv_DigitalNumber,
Ord_Type,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
Ord_SeriesName AS 套系名称, 
Ord_SeriesPrice AS 套系价格, 
dbo.fn_GetOrderArrears(Ord_Number) AS 欠款,
Ordpg_Sights as 拍摄名称,
dbo.fn_CheckPhotographyStatus(Ordpg_PhotographyStatus) AS 拍照状态,
dbo.fn_CheckUserIDGetUserName(Ordpg_ReservationPhotographyName) AS 摄影师,
dbo.fn_CheckUserIDGetUserName(Ordpg_ReservationMakeupArtist) AS 化妆师,
dbo.fn_CheckUserIDGetUserName(Ordpg_ReservationBootDivision) AS 引导师,
dbo.fn_CheckDateTime(Ordpg_ReservationPhotographyTime) AS 预定时间,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 结婚日期,
(select top 1 dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(OPlist_PickupTime)) from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = ''2'' and OPlist_PickupTime != null order by OPlist_PickupTime) AS 取件日期, 
dbo.fn_CheckUserIDGetUserName(TR_TracePersonID) AS 客服人员,
dbo.fn_CheckDateTime(TR_CreateDateTime) AS 录入时间,
TR_Communicationstatus AS 沟通状态,
TR_Remark AS 沟通备注,
(datediff(day, Ord_CreateDateTime, getdate())) AS 几天未完,
(SELECT COUNT(ID) AS Expr1 FROM dbo.tb_ErpOrdersPhotography AS tb_ErpOrdersPhotography_1 WHERE Ordpg_Number = Ordv_Number and Ordpg_PhotographyStatus != ''1'') AS Ord_ViceOrderCount
FROM dbo.tb_ErpOrder LEFT OUTER JOIN dbo.tb_ErpOrderDigital ON Ord_Number = Ordv_Number
Left join dbo.tb_ErpOrdersPhotography on Ordpg_ViceNumber = Ordv_ViceNumber
left join tempTB_AggregationCustomer on Ord_Number = GP_OrderNumber
Left join dbo.Vwp_GetLastClientTrackRecord on TR_CustomerGroupID = Ordv_Number
WHERE   (Ord_Class = ''1'') and Ordpg_PhotographyStatus != ''1''
' 
GO